{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:27:55.025358Z",
     "iopub.status.busy": "2023-11-13T13:27:55.025133Z",
     "iopub.status.idle": "2023-11-13T13:27:55.792919Z",
     "shell.execute_reply": "2023-11-13T13:27:55.792278Z",
     "shell.execute_reply.started": "2023-11-13T13:27:55.025331Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "stage = 'B'\n",
    "root = '../../../contest'\n",
    "\n",
    "df_train = pd.read_csv(os.path.join(root, 'train/GSLD_AGET_PAY.csv'))\n",
    "df_test = pd.read_csv(os.path.join(root, '{}/GSLD_AGET_PAY_{}.csv'.format(stage, stage)))\n",
    "\n",
    "save_path = '../data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:27:55.794334Z",
     "iopub.status.busy": "2023-11-13T13:27:55.794130Z",
     "iopub.status.idle": "2023-11-13T13:27:55.907469Z",
     "shell.execute_reply": "2023-11-13T13:27:55.906836Z",
     "shell.execute_reply.started": "2023-11-13T13:27:55.794309Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# unit_typ_list = df_train.UNIT_TYP_CD.value_counts(ascending=False).index\n",
    "# unit_typ_dic = {unit_typ_list[i]:i for i,_ in enumerate(unit_typ_list)}\n",
    "# prov_cd_list = df_train.PROV_CD.value_counts(ascending=False).index\n",
    "# prov_cd_dic = {prov_cd_list[i]:i for i,_ in enumerate(prov_cd_list)}\n",
    "# agen_cusno_list = df_train.drop_duplicates('CUST_NO').AGEN_CUSNO.value_counts().index[:957] # 单位人数≥10人\n",
    "# agen_cusno_dic = {agen_cusno_list[i]:i for i,_ in enumerate(agen_cusno_list)}\n",
    "\n",
    "# def prepro(df):\n",
    "#     df.PROV_CD = df.PROV_CD.fillna(prov_cd_list[0]) # 用众数填充\n",
    "#     df.PROV_CD = df.PROV_CD.map(prov_cd_dic)\n",
    "#     df.UNIT_TYP_CD = df.UNIT_TYP_CD.fillna(unit_typ_list[0]) # 用众数填充\n",
    "#     df.UNIT_TYP_CD = df.UNIT_TYP_CD.map(unit_typ_dic)\n",
    "#     df.TR_AMT = df.TR_AMT.apply(lambda x: round(pow(x/3.12,3),2))\n",
    "#     df.AGEN_CUSNO = df.AGEN_CUSNO.map(agen_cusno_dic).fillna(len(agen_cusno_dic)) # 对公客户编号 最后一类单位人数小于10人\n",
    "#     tmp_df = df.groupby('CUST_NO').agg(\n",
    "#         cust_cnt = ('AGEN_CUSNO', 'count'),\n",
    "#         agen_custno_avg = ('AGEN_CUSNO', 'mean'), # 对公客户编码\n",
    "#         tr_amt_sum = ('TR_AMT', 'sum'),\n",
    "#         tr_amt_avg = ('TR_AMT', 'mean'),\n",
    "#         tr_amt_min = ('TR_AMT', 'min'),\n",
    "#         tr_amt_max = ('TR_AMT', 'max'),\n",
    "#         prov_cd = ('PROV_CD', 'mean'),\n",
    "#         unit_typ_cd = ('UNIT_TYP_CD', 'mean'),\n",
    "#         tr_amt = ('TR_AMT', 'mean')\n",
    "#     ).reset_index()\n",
    "    \n",
    "#     df['date_new'] = pd.to_datetime(df['DATE'], format='%Y%m%d')\n",
    "#     df = df.drop_duplicates(subset=['DATE','CUST_NO'],keep='first')\n",
    "#     df['aget_date_diff'] = df.sort_values('DATE').groupby('CUST_NO')['date_new'].diff().dt.days\n",
    "#     df = df.sort_values('DATE').dropna()\n",
    "#     date_diff_df = df.groupby('CUST_NO').agg(\n",
    "# #         aget_date_diff_mean = ('aget_date_diff', 'mean'), # 发工资的间隔天数平均\n",
    "# #         aget_date_diff_max = ('aget_date_diff', 'max'),\n",
    "#         aget_date_diff_min = ('aget_date_diff', 'min')\n",
    "#     ).reset_index()\n",
    "    \n",
    "#     tmp_df = tmp_df.merge(date_diff_df, how='left', on='CUST_NO')\n",
    "#     return tmp_df\n",
    "\n",
    "unit_typ_list = df_train.UNIT_TYP_CD.value_counts(ascending=False).index\n",
    "unit_typ_dic = {unit_typ_list[i]:i for i,_ in enumerate(unit_typ_list)}\n",
    "prov_cd_list = df_train.PROV_CD.value_counts(ascending=False).index\n",
    "prov_cd_dic = {prov_cd_list[i]:i for i,_ in enumerate(prov_cd_list)}\n",
    "agen_cusno_list = df_train.drop_duplicates('CUST_NO').AGEN_CUSNO.value_counts(False)[:957].index\n",
    "agen_cusno_dic = {agen_cusno_list[i]:i for i,_ in enumerate(agen_cusno_list)}\n",
    "# agen_cusno_dic = df_train.AGEN_CUSNO.value_counts().to_dict() # 改为频次\n",
    "\n",
    "def prepro(df):\n",
    "    df.PROV_CD = df.PROV_CD.fillna(prov_cd_list[0]) # 用众数填充\n",
    "    df.PROV_CD = df.PROV_CD.map(prov_cd_dic)\n",
    "    df.UNIT_TYP_CD = df.UNIT_TYP_CD.fillna(unit_typ_list[0]) # 用众数填充\n",
    "    df.UNIT_TYP_CD = df.UNIT_TYP_CD.map(unit_typ_dic)\n",
    "    df.TR_AMT = df.TR_AMT.apply(lambda x: round(pow(x/3.12,3),2))\n",
    "    df.AGEN_CUSNO = df.AGEN_CUSNO.map(agen_cusno_dic).fillna(len(agen_cusno_dic))\n",
    "    tmp_df = df.groupby('CUST_NO').agg(\n",
    "        cust_cnt = ('AGEN_CUSNO', 'count'),\n",
    "#         agen_custno = ('AGEN_CUSNO', 'mean'), # 添加频次\n",
    "        tr_amt_sum = ('TR_AMT', 'sum'),\n",
    "        tr_amt_max = ('TR_AMT', 'max'),\n",
    "        prov_cd = ('PROV_CD', 'mean'),\n",
    "        unit_typ_cd = ('UNIT_TYP_CD', 'mean'),\n",
    "        tr_amt_avg = ('TR_AMT', 'mean')\n",
    "    ).reset_index()\n",
    "    return tmp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:27:55.908397Z",
     "iopub.status.busy": "2023-11-13T13:27:55.908217Z",
     "iopub.status.idle": "2023-11-13T13:27:56.508380Z",
     "shell.execute_reply": "2023-11-13T13:27:56.507749Z",
     "shell.execute_reply.started": "2023-11-13T13:27:55.908374Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train = prepro(df_train)\n",
    "df_test = prepro(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:27:56.509500Z",
     "iopub.status.busy": "2023-11-13T13:27:56.509306Z",
     "iopub.status.idle": "2023-11-13T13:27:56.884679Z",
     "shell.execute_reply": "2023-11-13T13:27:56.884087Z",
     "shell.execute_reply.started": "2023-11-13T13:27:56.509476Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train.to_csv(save_path+'/GSLD_AGET_PAY.csv', index=False)\n",
    "df_test.to_csv(save_path+'/GSLD_AGET_PAY_{}.csv'.format(stage), index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
